"""
author : wlingforever
file name : db_upload
last modified time:2022.10.8

description:
    upload image from database

"""
import os
import cv2
# import matplotlib.image as mpimg # mpimg 用于读取图片
import pymysql

# matplotlib.use('TkAgg')


with open("../docs/work_log.txt", 'r', encoding="UTF-8") as f:
    image_path = f.readline()
    # print(image_path)
    image_path = image_path.strip()
    print(image_path)

ims_path = "D:/Projects/innovation/RFO_Detection_System/yolov5-6.2/" + image_path
# find all images in dictionary,get its name
images_path = []
for root, dirs, files in os.walk(ims_path):
    for file in files:
        # print(file)
        # print(file)
        images_path.append(f'{ims_path}/{file}')
        # image_values ,targets_values = image_upload_data(f'../{image_path}/labels/{file}')
    break

# connect

username = 'rdmanager'
ur_password = 'rdm2022.'
# username = 'rdquery'
# ur_password = 'rdquery2022'

coon = pymysql.connect(host='localhost', port=3306,
                       user='rdmanager', password='rdm2022.',
                       database='rfo_detection', charset='utf8mb4')

try:
    # print(coon)
    # 获取游标对象
    with coon.cursor() as cursor:
        # 通过对象向数据库发sql语句，获取执行结果
        # tu = ("12", "n1", "n2")
        # print(images_path)
        for fi in images_path:
            image_name = fi.replace("D:/Projects/innovation/RFO_Detection_System/yolov5-6.2/" + image_path + "/", "")
            print(image_name)
            with open(fi, "rb") as f:
                # print(image_path)
                img_bin = f.read()
            #

            image_mat = cv2.imread(fi)
            # print(image_mat)
            affected_rows_i = cursor.execute(
                'insert into tb_image_mat_data (image_name,im_row, im_col,image_mat) values (%s,%s,%s,%s)',
                (image_name, len(image_mat), len(image_mat[0]), img_bin)
            )
            if affected_rows_i == 1:
                print("添加image成功!")
    print("database operation successfully")

    # 提交
    coon.commit()
except pymysql.MySQLError as err:
    print("insert error!")
    print(err)
    coon.rollback()
finally:
    coon.close()

